package de.stalinski.db;

import de.stalinski.util.HTMLUtil;
import de.stalinski.util.Logging;
import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class JournalConnector
{

    private static String _sUsr = "root";

    private static String _sPwd = "admin";

    private static String _sDB = "frbrUsers";

    private static String _sTable = "journal";

    private static Connection _connection;

    private static String _sHost = "localhost";

    private static String _sConn = "jdbc:mysql://" + _sHost + ":3306/" + _sDB;

    public JournalConnector()
    {
        try {
            Class.forName("com.mysql.jdbc.Driver");
        } catch ( ClassNotFoundException ex ) {
            Logging.log(Logging.ERROR, ex, this.getClass().getName());
        }
        try {
            if ( _connection == null ) {
                _connection = DriverManager.getConnection(_sConn, _sUsr, _sPwd);
            }
        } catch ( SQLException ex ) {
            Logging.log(Logging.ERROR, ex, this.getClass().getName());
        }
    }

    public String showTotalJournal(int id)
    {
        HTMLUtil hU = new HTMLUtil();
        String out = hU.HTML_TABLE_DEF_START;
        out += hU.HTML_TABLE_HEAD_START;
        out += hU.createHeader("Datum");
        out += hU.createHeader("ID");
        out += hU.createHeader("Buchungsnummer");
        out += hU.createHeader("Betreff");
        out += hU.createHeader("Kategorie");
        out += hU.createHeader("Kontonummer");
        out += hU.createHeader("Konto Klartext");
        out += hU.createHeader("Betrag");
        out += hU.HTML_TABLE_HEAD_END;
        try {
            Class.forName("com.mysql.jdbc.Driver");
            try {
                _connection = DriverManager.getConnection(_sConn, _sUsr, _sPwd);
            } catch ( SQLException ex ) {
                Logging.log(Logging.ERROR, ex, this.getClass().getName());
            }
        } catch ( ClassNotFoundException ex ) {
            Logging.log(Logging.ERROR, ex, this.getClass().getName());
        }

        try {
            Statement st = null;
            ResultSet rs = null;
            st = _connection.createStatement();
            if ( id == -1 ) {
                rs = st.executeQuery("select * from " + _sTable);
            } else {
                rs = st.executeQuery(
                        "select * from " + _sTable + " WHERE ACCOUNT='" + id + "'");
            }
            hU.setResultSet(rs);
            out += hU.HTML_TABLE_BODY_START;

            double vOut = 0.0;
            double vIn = 0.0;
            while ( rs.next() ) {
                out += hU.HTML_TABLE_TR_START;
                out += hU.convertToTableDateField("DATE");
                out += hU.convertToTableField("ID");
                out += hU.convertToTableField(("BOOK_ID"));
                out += hU.convertToTableField(("SUBJECT"));
                out += hU.convertToTableField(("CATEGORY"));
                out += hU.convertToTableField(("ACCOUNT"));
                out += hU.convertToTableField(("ACCOUNT_CLEAR"));
                out += hU.convertToTableField(("VALUE"));
                out += hU.HTML_TABLE_TR_END;
                if ( rs.getDouble("VALUE") > 0 ) {
                    vIn += rs.getDouble("VALUE");
                } else {
                    vOut += rs.getDouble("VALUE");
                }
            }
            out += hU.HTML_TABLE_TR_START;
            out += "<td colspan=8>&nbsp;</td>";
            out += hU.HTML_TABLE_TR_END;

            out += hU.HTML_TABLE_TR_START;
            out += "<td colspan=7>Einnahmen:</td>";
            out += "<td>" + vIn + "&nbsp;&euro;</td>";
            out += hU.HTML_TABLE_TR_END;

            out += hU.HTML_TABLE_TR_START;
            out += "<td colspan=7>Ausgaben:</td>";
            out += "<td>" + vOut + "&nbsp;&euro;</td>";
            out += hU.HTML_TABLE_TR_END;

            out += hU.HTML_TABLE_TR_START;
            out += "<td colspan=7>Kontostand:</td>";
            out += "<td>" + (new BigDecimal(vIn+vOut).setScale(2,BigDecimal.ROUND_CEILING)) + "&nbsp;&euro;</td>";
            out += hU.HTML_TABLE_TR_END;

            out += hU.HTML_TABLE_BODY_END;
            out += hU.HTML_TABLE_DEF_END;
        } catch ( SQLException ex ) {
            Logging.log(Logging.ERROR, ex, this.getClass().getName());
        }

        return out;
    }

}
